• 4.1 DDL语句
  • 4.1.1 创建表(CREATE TABLE)
  • 4.1.2 删除表
  • 4.1.3 重命名表
  • 4.1.4 修改表的注释
  • 4.1.5 缓存表
  • 4.1.6 删除缓存表
  • 4.1.7 添加分区
  • 4.1.8 删除分区
  • 4.1.9 添加列
  • 4.1.10 修改列、分区注释
  • 4.1.11 修改表的生命周期属性
  • 4.1.12 创建视图
  • 4.1.13 修改视图名称
  • 4.1.14 修改视图select 语句
  • 4.1.15 删除视图
  • 4.1.16 描述表
  • 4.1.17 查看表分区
• 4.2 DML语句
  • 4.2.1 insert
  • 4.2.2 Query语句
    • 4.2.2 select
  • 4.2.3 join
  • 4.2.4 Lateral View
• 4.3 其他语句
  • 4.3.1 load
  • 4.3.2 export
  • 4.3.3 read
  • 4.3.4 merge

4 支持的SQL 语法

Hive SQL 语法很多，DataOceam当前限制只支持以下SQL语法：

1. ALTERTABLE_RENAME,ALTERTABLE_ADDCOLS,ALTERTABLE_RENAMECOL,ALTERTABLE_TOUCH,
2. ALTERTABLE_DROPPARTS,ALTERTABLE_ADDPARTS,ALTERTABLE_RENAMEPART,ALTERTABLE_PARTCOLTYPE,
3. CREATETABLE,CREATETABLE_AS_SELECT,DROPTABLE,DESCTABLE,
4. SELECT,INSERT_OVERWRITE,INSERT_INTO,CACHE,UNCACHE
5. LOAD,EXPORT

4.1 DDL语句

4.1.1 创建表(CREATE TABLE)

语法格式

create table [if not exists] table_name
 [like table_name2]
 [(col_name data_type [comment col_comment], ...)]
 [comment table_comment]
 [partitioned by (col_name data_type [comment col_comment], ...)]
 lifecycle days
 [as select_statement]

说明

1. 表名与列名均对大小写不敏感。分区表必须以'_dt'、'_mt'、'_ht'、'_wt'、'_ft'结尾对应天,月，时，周，分。
2. 在创建表时，如果不指定if not exists选项而存在同名表，则返回出错；若指定此选项，则无论是否存在同名表，即使原表结构与要创建的目标表结构不一致，均返回成功。已存在的同名表的元信息不会被改动。
3. 数据类型只能是：bigint，double，boolean，datetime及string。表名，列名中不能有特殊字符，只能用英文的a-z，A-Z及数字和下划线_ 且以字母开头，名称的长度不超过128字节。
4. Partitioned by 指定表的分区字段，分区字段有以下限制。 1) 分区字段必须包括ds字段。 2) 分区字段类型目前仅支持string类型。 3) 分区值不可以有双字节字符(如中文)，必须是以英文字母a-z，A-Z开始后可跟字母数字，名称的长度不超过128字节。 如果名称中出现了\t，\n等特殊字符会导致分区中的数据无法读出，允许的字符包括：空格 ‘ ‘，冒号’:’，下划线’_’，美元符’$’，井号’#’，点’.’，感叹号’!’和’@’。 4) 当利用分区字段对表进行分区时，新增分区、更新分区内数据和读取分区数据均不需要做全表扫描，可以提高处理效率。 5) 分区字段默认最多设置3个。
5. 注释内容是长度不超过1024字节的有效字符串。
6. lifecycle指明此表的生命周期.
7. 目前，在表中建的分区层次不能超过5级。
8. create as select 语法创建的表都是临时表，每天早上5点后会定期清理。
9. create table like可以快速创建目标表相同的表结构，配合insert select from语法可以快速将数据同步。

实例

--创建分区表
create table if not exists sale_detail_dt(
 shop_name     string,
 customer_id   string,
 total_price   double)
partitioned by (ds string,sale_date string,region string)
lifecycle 7;

--创建临时表并从老的表拷贝数据
create table tbl_sale_detail_ctas1 as
   select * from sale_detail;

--创建表从老的表拷贝数据，并添加两个列
create table tbl_sale_detail_ctas2 as
   select shop_name,
       customer_id,
       total_price,
       '2013' as sale_date,
       'China' as region
   from sale_detail;

--根据目标表结构创建表   
create table people_msg_test1 like people_msg_test;
--全量拷贝数据
insert overwrite table people_msg_test1 select * from people_msg_test;

4.1.2 删除表

语法格式

drop table [if exists] table_name;

说明

1. 如果不指定if exists选项而表不存在，则返回异常；若指定此选项，无论表是否存在，皆返回成功。

实例

create table sale_detail_drop like sale_detail;

--若表存在，成功返回；若不存在，异常返回；
drop table sale_detail_drop;

--无论是否存在sale_detail_drop2表，均成功返回；
drop table if exists sale_detail_drop2;

4.1.3 重命名表

语法格式

alter table table_name rename to new_table_name;

说明

1. rename操作仅修改表的名字，不改动表中的数据。
2. 如果已存在与new_table_name同名表，报错。
3. 如果table_name不存在，报错。

实例

create table sale_detail_rename1 like sale_detail;

alter table sale_detail_rename1 rename to sale_detail_rename2;

4.1.4 修改表的注释

语法格式4

alter table table_name set comment 'tbl comment';

说明

1. table_name必须是已存在的表；comment最长1024字节；

实例

alter table sale_detail set comment 'new coments for table sale_detail';

4.1.5 缓存表

语法格式

cache table table_name

说明

1. sql 优化缓存表到内存

4.1.6 删除缓存表

语法格式

uncache table table_name

说明

1. 取消内存缓存表数据，回收内存资源

4.1.7 添加分区

语法格式

alter table table_name add [if not exists] partition partition_spec

partition_spec:
 : (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)

说明

1. 如果未指定if not exists而同名的分区已存在，则出错返回。
2. 目前ODPS支持的分区数量上限为4万。
3. 对于多级分区的表，如果想添加新的分区，必须指明全部的分区值。
4. 分区字段ds必须存在。

实例

-- 成功添加分区，用来存储2013年12月杭州地区的销售记录。
alter table sale_detail_dt add if not exists partition (ds="1", sale_date='201312', region='hangzhou');

-- 成功添加分区，用来存储2013年12月上海地区的销售记录。
alter table sale_detail_dt add if not exists partition (ds="1",sale_date='201312', region='shanghai');

4.1.8 删除分区

语法格式

alter table table_name drop [if exists] partition_spec;

partition_spec:
: (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)

说明

1. 如果分区不存在且未指定if exists，则出错返回。

实例

-- 成功删除2013年12月杭州分区的销售。
alter table sale_detail drop partition(ds="1",sale_date='201312',region='hangzhou');

4.1.9 添加列

语法格式

alter table table_name add columns (col_name1 type1, col_name2 type2...)

说明

1. 列的数据类型只能是：bigint，double，boolean，datetime及string类型。 实例

   alter table sale_detail_dt add columns (location1 string, location2 string);
   

4.1.10 修改列、分区注释

语法格式

alter table table_name change column col_name comment 'comment';

说明

1. comment内容最长1024字节

4.1.11 修改表的生命周期属性

语法格式

alter table table_name set lifecycle days;

说明

1. days 参数为生命周期时间，只接受正整数。单位：天 如果表table_name是非分区表，自最后一次数据被修改开始计算，经过days天后数据仍未被改动，则此表无需用户干预，将会被ODPS自动回收(类似drop table操作)。在ODPS中，每当表的数据被修改后，表的LastDataModifiedTime将会被更新，因此，ODPS会根据每张表的LastDataModifiedTime以及lifecycle的设置来判断是否要回收此表。如果table_name是分区表，则根据各分区的LastDataModifiedTime判断该分区是否该被回收。关于LastDataModifiedTime的介绍请参考 查看表信息 。 不同于非分区表，分区表的最后一个分区被回收后，该表不会被删除。生命周期只能设定到表级别，不能再分区级设置生命周期。创建表时即可指定生命周期，详情请参阅 创建表(CREATE TABLE) 。

实例

-- 新建test_lifecycle表，生命周期为100天。
create table test_lifecycle(key string) lifecycle 100;

-- 修改test_lifecycle表，将生命周期设为50天。
alter table test_lifecycle set lifecycle 50;

4.1.12 创建视图

语法格式

create view [if not exists] view_name
[comment '']
as select ....;

说明

1. 视图名称以view_作为前缀
2. 视图不需要申请权限，可以访问任何人的创建的视图，但需要申请视图中引用表的权限

实例

create view if not exists view_users_view
comment 'Hello'
as select * from test_users_dt;

4.1.13 修改视图名称

语法格式

ALTER view view_old_name RENAME TO view_new_name

说明

1. rename操作仅修改视图的名字，不改动视图中的数据。
2. 如果已存在与view_new_name同名视图，报错。
3. 如果view_old_name不存在，报错。

实例

ALTER view view_users_view RENAME TO view_users_view1

4.1.14 修改视图select 语句

语法格式

ALTER view view_name as select ...;

实例

ALTER view view_users_view as select name from test_users_dt;

4.1.15 删除视图

语法格式

drop view view_name;

说明

1. 如果视图不存在，则返回异常

实例

drop view view_users_view;

4.1.16 描述表

语法格式

desc sale_detail_dt

实例

show partitions sale_detail_dt

4.1.17 查看表分区

语法格式

show partitions table_name

实例

show partitions sale_detail_dt

4.2 DML语句

4.2.1 insert

语法格式

INSERT INTO [TABLE] [db_name.]table_name [PARTITION part_spec] select_statement

INSERT OVERWRITE TABLE [db_name.]table_name [PARTITION part_spec] select_statement

part_spec:
  : (part_col_name1=val1 [, part_col_name2=val2, ...])

实例

insert into people_msg_test values('1','1','1','111')

insert overwrite table people_msg_test1 select * from people_msg_test;

4.2.2 Query语句

4.2.2 select

语法格式

SELECT [hints, ...] [ALL|DISTINCT] named_expression[, named_expression, ...]
  FROM relation[, relation, ...]
  [lateral_view[, lateral_view, ...]]
  [WHERE boolean_expression]
  [aggregation [HAVING boolean_expression]]
  [ORDER BY sort_expressions]
  [CLUSTER BY expressions]
  [DISTRIBUTE BY expressions]
  [SORT BY sort_expressions]
  [WINDOW named_window[, WINDOW named_window, ...]]
  [LIMIT num_rows]

named_expression:
  : expression [AS alias]

relation:
  | join_relation
  | (table_name|query|relation) [sample] [AS alias]
  : VALUES (expressions)[, (expressions), ...]
        [AS (column_name[, column_name, ...])]

expressions:
  : expression[, expression, ...]

sort_expressions:
  : expression [ASC|DESC][, expression [ASC|DESC], ...]

实例

create table people_msg_test (
    zjhm    string  comment '证件号码',
    xm  string  comment '姓名',
    xb string  comment  '性别',
    height int comment '身高',
    phone   string  comment '手机'
)
comment     '人信息'
lifecycle 365;

insert overwrite table people_msg_test values('330103198001010001','张一','男',180,'13712345678'),
('330103198001010002','张二','男',172,'13712345688'),
('330103198001010003','张三','男',168,'13712345778'),
('330103198001010004','张四','女',162,'13812345678'),
('330103198001010005','张五','女',153,'13912345678');

--query
SELECT zjhm, xm FROM people_msg_test WHERE zjhm='330103198001010001'
SELECT DISTINCT zjhm, xm FROM people_msg_test WHERE xb='男' LIMIT 2
SELECT * FROM VALUES (1, 2, 3) AS (width, length, height)
SELECT * FROM VALUES (1, 2, 3), (2, 3, 4) AS (width, length, height)
SELECT * FROM people_msg_test ORDER BY zjhm
SELECT * FROM people_msg_test DISTRIBUTE BY zjhm SORT BY zjhm
SELECT * FROM people_msg_test CLUSTER BY zjhm

--sample
SELECT * FROM people_msg_test TABLESAMPLE (2 ROWS)
SELECT * FROM people_msg_test TABLESAMPLE (25 PERCENT)

4.2.3 join

实例

create table vechile_msg_test (
    vehicle_id    string  comment '车id',
    vehicle_type  string  comment '车类型',
    color  string  comment  '车颜色',
    owner_zjhm    string  comment '车主证件号码'
)
comment     '车信息'
lifecycle 365;

insert overwrite table vechile_msg_test values('1','宝马','白','330103198001010001'),('2','奥迪','白','330103198001010003')

SELECT * FROM people_msg_test INNER JOIN vechile_msg_test ON people_msg_test.zjhm = vechile_msg_test.owner_zjhm
SELECT * FROM people_msg_test LEFT JOIN vechile_msg_test ON people_msg_test.zjhm = vechile_msg_test.owner_zjhm
SELECT * FROM people_msg_test RIGHT JOIN vechile_msg_test ON people_msg_test.zjhm = vechile_msg_test.owner_zjhm

4.2.4 Lateral View

实例

--每条数据添加多个列
SELECT * FROM people_msg_test LATERAL VIEW explode(Array(1, 2, 3)) my_view
SELECT xm, my_view.grade FROM people_msg_test LATERAL VIEW explode(Array(1, 2, 3)) my_view AS grade

4.3 其他语句

4.3.1 load

导入数据-加载json、csv或excel文件数据作为spark 临时表DataSource
语法格式

load data '文件路径' table [表名] [options(key=value)]

说明

1. options为可选属性，其中的取值和spark.read中的option取值相同
2. 文件路径为hdfs上面的路径，同时csv文件的列名不能包含空格
3. 以下为option可选参数
4. header: when set to true the first line of files will be used to name columns and will not be included in data. All * types will be assumed string. Default value is false.
5. delimiter: by default columns are delimited using ,, but delimiter can be set to any character
6. quote: by default the quote character is ", but can be set to any character. Delimiters inside quotes are ignored
7. escape: by default the escape character is , but can be set to any character. Escaped quote characters are ignored
8. parserLib: by default it is "commons" can be set to "univocity" to use that library for CSV parsing.
9. mode: determines the parsing mode. By default it is PERMISSIVE. Possible values are: 。PERMISSIVE: tries to parse all lines: nulls are inserted for missing tokens and extra tokens are ignored. 。DROPMALFORMED: drops lines which have fewer or more tokens than expected or tokens which do not match the schema 。FAILFAST: aborts with a RuntimeException if encounters any malformed line
10. charset: defaults to 'UTF-8' but can be set to other valid charset names
11. inferSchema: automatically infers column types. It requires one extra pass over the data and is false by default
12. comment: skip lines beginning with this character. Default is "#". Disable comments by setting this to null.
13. nullValue: specifies a string that indicates a null value, any fields matching this string will be set as nulls in the DataFrame
14. dateFormat: specifies a string that indicates the date format to use when reading dates or timestamps. Custom date formats follow the formats at java.text.SimpleDateFormat. This applies to both DateType and TimestampType. By default, it is null which means trying to parse times and date by java.sql.Timestamp.valueOf() and java.sql.Date.valueOf().

实例，将scv导入临时表

-- sql作业中(注意：要两个语句同时选中执行，tdl_spark_test表只是临时表，执行完操作后就会消失，仅做分析使用，type='login'只是一个举例)
load data '/user/datacompute/platformtool/resources/169/latest/dc_load_data.csv' table tdl_spark_test options( delimiter=',');
select * from tdl_spark_test where type='login';

-- load 命令数据不落磁盘存储，如果需要存储的表，使用如下方式(注意：要两个语句同时选中执行，tdl_data_table表为需要插入数据的表，需要提前创建好)
load data '/user/datacompute/platformtool/resources/169/latest/dc_load_data.csv' table tdl_spark_test options( delimiter=',');
insert overwrite table tdl_data_table select * from tdl_spark_test where type='login';

-- load 将数据加载进一张七天生命周期的临时表
load data '/user/datacompute/platformtool/resources/169/latest/dc_load_data.csv' table tdl_spark_test options(delimiter=',');
create table tdl_data_table lifecycle 7 select * from tdl_spark_test;

实例，将excel导入临时表(数量量少时使用)

-- sql作业中(注意：要两个语句同时选中执行，tdl_spark_test表只是临时表，执行完操作后就会消失)
-- 导入excel 参数说明参考文档： https://github.com/crealytics/spark-excel
load data '/user/datacompute/platformtool/resources/169/latest/dc_load_data.xlsx' table tdl_spark_test ;
select * from tdl_spark_test ;

-- load 命令数据不落磁盘存储，如果需要存储的表，使用如下方式(注意：要两个语句同时选中执行，tdl_data_table表为需要插入数据的表，需要提前创建好)
load data '/user/datacompute/platformtool/resources/169/latest/dc_load_data.xlsx' table tdl_spark_test ;
insert overwrite table tdl_data_table select * from tdl_spark_test;

-- load 将数据加载进一张七天生命周期的临时表
load data '/user/datacompute/platformtool/resources/169/latest/dc_load_data.xlsx' table tdl_spark_test ;
create table tdl_data_table lifecycle 7 select * from tdl_spark_test;

实例，导入zip文件

--zip文件里只能存放多个csv和json文件，不支持json、csv和excel文件混放的情况
-- load 将数据加载进一张七天生命周期的临时表
load data '/user/datacompute/users/xiuxiu.zheng/csv_test.zip' table tdl_spark_test options(delimiter=',',header='true') ;
create table tdl_data_table lifecycle 7 select * from tdl_spark_test;

4.3.2 export

导出表数据为csv文件或excel文件
注意：
-1、目前excel文件导入性能不是那么好，故只建议在数据量比较少的情况下用excel导出，数据比较多的情况还是建议使用csv文件导出

-2、如果希望导出数据切分为多个文件压缩打包，文件后缀约定为：zip， 指定切分文件数量通过在options中指定参数: fileCount。

语法格式

export table tablename [PARTITION (part_column="value"[, ...])] TO 'export_file_name.csv' [options(key=value)]

说明

1. 其中的表既可以为hive表，也可以是同一个作业中通过createOrReplaceTempView创建的临时表，保存后文件在资源管理->我的资源 目录下面，
2. 导出csv文件默认是以逗号作为字段分割，如果需要指定分割符，可以在options选项中指定delimiter，options可选属性同Load属性
3. 目前excel文件导入性能不是那么好，故只建议在数据量比较少的情况下用excel导出，数据比较多的情况还是建议使用csv文件导出
4. 如果希望导出数据切分为多个文件压缩打包，文件后缀约定为：zip， 指定切分文件数量通过在options中指定参数: fileCount。

实例，导出csv文件

--delimiter表示csv文件分隔符
export table raw_activity_flat PARTITION (year=2018, month=3, day=12) TO 'activity_20180312.csv' options(delimiter=';')

--覆盖原文件导出, 添加参数：overwrite = true
export table raw_activity_flat PARTITION (year=2018, month=3, day=12) TO 'activity_20180312.csv' options(delimiter=';',overwrite=true)

--默认不支持array,map,struct 复杂类型的数据导出，添加参数：complexTypeToJson = true，复杂类型数据转为json string 导出
export table raw_activity_flat PARTITION (year=2018, month=3, day=12) TO 'activity_20180312.csv' options(delimiter=';',complexTypeToJson = true)

--这段代码只能在python 作业中使用
sparkSession.sql("select email, idnumber, wifi from raw_activity_flat where year=2018 and month=3 and partnerCode='qunaer' ").createOrReplaceTempView("tdl_raw_activity_qunaer");
sparkSession.sql("export table tdl_raw_activity_qunaer TO 'activity_20180312.csv'")

--如果导出 zip 压缩文件，文件名后缀为.csv.zip

实例，导出excel文件(数量量少时使用)

-- 导出excel 参数说明参考文档： https://github.com/crealytics/spark-excel
export table raw_activity_flat PARTITION (year=2018, month=3, day=12) TO 'activity_20180312.xlsx'

--覆盖原文件导出, 添加参数：overwrite = true
export table raw_activity_flat PARTITION (year=2018, month=3, day=12) TO 'activity_20180312.xlsx' options(overwrite=true)

--这段代码只能在python 作业中使用
sparkSession.sql("select email, idnumber, wifi from raw_activity_flat where year=2018 and month=3 and partnerCode='qunaer' ").createOrReplaceTempView("tdl_raw_activity_qunaer");
sparkSession.sql("export table tdl_raw_activity_qunaer TO 'activity_20180312.xlsx'")

实例，导出json文件

--导出文件名后缀只能是：.json 或 .json.zip
--后缀.json.zip 生成数据文件是一个压缩文件，可以通过fileCount 参数指定导出的文件数量。
export table raw_activity_flat PARTITION (year=2018, month=3, day=12) TO 'activity_20180312.json' options(overwrite=true)
export table raw_activity_flat PARTITION (year=2018, month=3, day=12) TO 'activity_20180312.json.zip' options(overwrite=true, fileCount=3)

4.3.3 read

快速读取数据，直接读取文件数据，不需要通过spark计算启动task获取数据，速度上有很大优势，如果不带条件的查询数据，推荐使用read 语句
语法格式

read table [表名] limit [读取数据量] --非分区表
read table [表名] PARTITION (分区信息) limit [读取数据量] --分区表，分区表必须指定分区

实例 导出csv文件

read table test_users_dt PARTITION (ds=20170205) limit 100;
read table read_users_test limit 100;

4.3.4 merge

表碎片小文件合并为一个文件，分区表可以指定分区，如果不带分区，则依次合并每一个分区

语法格式

merge table [表名] [options (fileCount=合并后文件数量)]  --非分区表
merge table [表名] PARTITION (分区信息) [options (fileCount=合并后文件数量)] --分区表

示例

merge table test_users_dt PARTITION (ds=20170205) options(fileCount=1);
merge table read_users_test;